create view [dbo].[vFRDonation] as

--------------- Select single gift and pledge from FRDonationTemp -------------

select max(ID) ID,
max(OriginalTransNumber) OriginalTransNumber,
max(TransactionNumber) TransactionNumber,
'' as InvoiceRefNum,
'Fund Raising' as SourceSystem,
max(TransactionDate) TransactionDate,
sum(Amount) Amount,
max(Product) Product,
max(Appeal) Appeal,
max(Campaign) Campaign,
max(Fund) Fund,
max(GiftType) GiftType,
max(MatchOrSoftCredit) MatchOrSoftCredit,
-- max(PaymentType) PaymentType,
case     when max(PaymentType) = 0 then 'Cash'  
    when max(PaymentType) = 1 then 'Credit Card'  
    when max(PaymentType) = 2 then 'In Kind'
    else ''
    end
as PaymentType,
max(AdjustmentFlag) AdjustmentFlag
from vFRDonationTemp
group by ID, TransactionNumber, Fund, Product

union

------------- select meeting gift   ------------

select max(Trans.ST_ID)ID,
min(Trans.TRANS_NUMBER)as OriginalTransNum,
min(Trans.TRANS_NUMBER)TransactionNumber,
max(Trans.INVOICE_REFERENCE_NUM) InvoiceRefNum,
'Event' as SourceSystem,
max(Trans.TRANSACTION_DATE)TransactionDate,
(sum(Trans.AMOUNT) * -1) as Amount,
max(Trans.PRODUCT_CODE) Product,
max(Trans.SOURCE_CODE)Appeal,
max(Trans.CAMPAIGN_CODE)Campaign,
Max(Trans.GL_ACCT_ORG_CODE) Fund,
'Gift' as GiftType,
'' as MatchOrSoftCredit,
'' as PaymentType,
'' as AdjustmentFlag
from Trans
where Trans.SOURCE_SYSTEM = 'MEETING'
and  Trans.JOURNAL_TYPE = 'IN'  and  Trans.TRANSACTION_TYPE = 'DIST'
and  Trans.IS_FR_ITEM = 1
and Trans.POSTED >= 2
group by Trans.ST_ID, Trans.INVOICE_REFERENCE_NUM, Trans.GL_ACCT_ORG_CODE, Trans.PRODUCT_CODE

union

----------- Select Soft Credit ---------------


select Max(Trans_SoftCredit.SOFT_CREDIT_ID) ID,
max(Trans_SoftCredit.TRANS_NUMBER)OriginalTransNum,
max(Trans_SoftCredit.TRANS_NUMBER)TransactionNumber,
'' as InvoiceRefNum,
'Fund Raising' as SourceSystem,
max(Activity.TRANSACTION_DATE) as TransactionDate,
sum(Trans_SoftCredit.AMOUNT) as Amount,
max(Trans_SoftCredit.PRODUCT_CODE) as Product,
max(Activity.SOURCE_CODE) as Appeal,
max(Activity.CAMPAIGN_CODE) as Campaign,
max(Activity.ORG_CODE) Fund,
(case when max(Activity.ACTIVITY_TYPE) = 'GIFT' then 'Gift' else 'Pledge' end) as GiftType,
'Soft Credit' as MatchOrSoftCredit,
case     when max(Cash_Accounts.ACCOUNT_TYPE) = 0 then 'Cash'  
    when max(Cash_Accounts.ACCOUNT_TYPE) = 1 then 'Credit Card'  
    when max(Cash_Accounts.ACCOUNT_TYPE) = 2 then 'In Kind'
    else ''
    end
as PaymentType,
-- max(Cash_Accounts.ACCOUNT_TYPE) as PaymentType,
'' as AdjustmentFlag
from Trans_SoftCredit
inner join Activity on Activity.SEQN = Trans_SoftCredit.ORIGINATING_ACTIVITY_SEQN
inner join Trans on Trans.TRANS_NUMBER = Trans_SoftCredit.TRANS_NUMBER
left outer join Cash_Accounts on Trans.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
where Activity.SOURCE_SYSTEM='FR'
and Trans.LINE_NUMBER = 1 AND Trans.SUB_LINE_NUMBER = 1
and Trans.POSTED >= 2
group by Trans_SoftCredit.SOFT_CREDIT_ID, Trans_SoftCredit.TRANS_NUMBER, Activity.ORG_CODE, Trans_SoftCredit.PRODUCT_CODE

GO
GRANT REFERENCES ON  [dbo].[vFRDonation] TO [IMIS]
GRANT SELECT ON  [dbo].[vFRDonation] TO [IMIS]
GRANT INSERT ON  [dbo].[vFRDonation] TO [IMIS]
GRANT DELETE ON  [dbo].[vFRDonation] TO [IMIS]
GRANT UPDATE ON  [dbo].[vFRDonation] TO [IMIS]
GO